/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package database;

import bean.CategorySubCategoryBean;
import bean.FriendBean;
import bean.UserBean;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author user
 */
public class DataManager {
    protected final String driver = "com.mysql.jdbc.Driver";
    protected final String dbName = "maninrelaychat";
    protected final String dbUserName = "root";
    protected final String dbPassword = "";

    protected Connection getConnection()
           throws  Exception{
            Class.forName(driver);
            Connection connection = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/" + dbName, dbUserName, dbPassword);
        return connection;
    }

    public void putConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                System.out.println("Enable to close DB connection");
                e.printStackTrace();
            }
        }
    }
    
    public UserBean getUserData(String userName) throws Exception {
        UserBean userBean = new UserBean();
        Connection conn = getConnection();
        if (conn != null) {
            ResultSet rs = null;
            Statement stmt = null;
            try {
                String strQuery = "SELECT * FROM user WHERE username='" + userName + "' ";
                       
                stmt = (Statement) conn.createStatement();
                rs = stmt.executeQuery(strQuery);

                while (rs.next()) {
                    userBean.setName(rs.getString("name"));
                    userBean.setUserName(rs.getString("username"));
                    userBean.setPassword(rs.getString("password"));
                    userBean.setEmail(rs.getString("email"));
                    userBean.setMaxRoom(rs.getString("max_room"));
                    userBean.setStatus((rs.getString("ban")));
                }
            }//end of try
            catch (SQLException ex) {
                System.out.println("SQLException:" + ex.getMessage());
            } finally {
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                putConnection(conn);
            }//end of finally
        }//end of if
        return userBean;
    }

     public CategorySubCategoryBean getCategorySubCategoryData(String catName) throws Exception {
        CategorySubCategoryBean cat = new CategorySubCategoryBean();
        Connection conn = getConnection();
        if (conn != null) {
            ResultSet rs = null;
            Statement stmt = null;
            try {
                String strQuery = "SELECT * FROM category WHERE name='" + catName + "' ";

                stmt = (Statement) conn.createStatement();
                rs = stmt.executeQuery(strQuery);

                while (rs.next()) {
                    cat.setName(rs.getString("name"));
                    cat.setModerator(rs.getString("moderator"));
                    cat.setParent(rs.getString("parent"));
                }
            }//end of try
            catch (SQLException ex) {
                System.out.println("SQLException:" + ex.getMessage());
            } finally {
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                putConnection(conn);
            }//end of finally
        }//end of if
        return cat;
    }

     public ArrayList<FriendBean> getAllFriendsFromUser(String userName) throws Exception {
        ArrayList<FriendBean> result = new ArrayList<FriendBean>();

        Connection conn = getConnection();
        if (conn != null) {
            ResultSet rs = null;
            Statement stmt = null;
            try {
                String strQuery = "SELECT * FROM friend WHERE username ='"+userName+"' ";

                stmt = (Statement) conn.createStatement();
                rs = stmt.executeQuery(strQuery);

                while (rs.next()) {
                    FriendBean friendBean = new FriendBean();

                    friendBean.setUserName(rs.getString("username"));
                    friendBean.setFriendTo(rs.getString("friend_to"));
                    result.add(friendBean);
                }
            }//end of try
            catch (SQLException ex) {
                System.out.println("SQLException:" + ex.getMessage());
            } finally {
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                putConnection(conn);
            }//end of finally
        }//end of if
        return result;
    }
     
    public ArrayList<UserBean> getAllUserData() throws Exception {
        ArrayList<UserBean> result = new ArrayList<UserBean>();
        
        Connection conn = getConnection();
        if (conn != null) {
            ResultSet rs = null;
            Statement stmt = null;
            try {
                String strQuery = "SELECT * FROM user ";

                stmt = (Statement) conn.createStatement();
                rs = stmt.executeQuery(strQuery);

                while (rs.next()) {
                    UserBean userBean = new UserBean();

                    userBean.setName(rs.getString("name"));
                    userBean.setUserName(rs.getString("username"));
                    userBean.setPassword(rs.getString("password"));
                    userBean.setEmail(rs.getString("email"));
                    userBean.setMaxRoom(rs.getString("max_room"));
                    userBean.setStatus(rs.getString("ban"));
                    result.add(userBean);
                }
            }//end of try
            catch (SQLException ex) {
                System.out.println("SQLException:" + ex.getMessage());
            } finally {
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                putConnection(conn);
            }//end of finally
        }//end of if
        return result;
    }

    public ArrayList<CategorySubCategoryBean> getAllCategory() throws Exception {
        ArrayList<CategorySubCategoryBean> result = new ArrayList<CategorySubCategoryBean>();

        Connection conn = getConnection();
        if (conn != null) {
            ResultSet rs = null;
            Statement stmt = null;
            try {
                String strQuery = "SELECT * FROM category ";

                stmt = (Statement) conn.createStatement();
                rs = stmt.executeQuery(strQuery);

                while (rs.next()) {
                    CategorySubCategoryBean catBean = new CategorySubCategoryBean();

                    catBean.setName(rs.getString("name"));
                    catBean.setParent(rs.getString("parent"));
                    catBean.setModerator(rs.getString("moderator"));
                    result.add(catBean);
                }
            }//end of try
            catch (SQLException ex) {
                System.out.println("SQLException:" + ex.getMessage());
            } finally {
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                putConnection(conn);
            }//end of finally
        }//end of if
        return result;
    }
}
